/*
 * To change this template, choose Tools | Templates
 * and open the template in the editor.
 */

/*
 * To change this template, choose Tools | Templates
 * and open the template in the editor.
 */

package DBLayer;
import ModelLayer.*;
import java.sql.*;
import java.util.ArrayList;

/**
 *
 * @author Sharif
 */
public class DBSupplier implements IFDBSupplier {
     private  Connection con;
    /** Creates a new instance
     * of DBSupplier */
    public DBSupplier() {
      con = DBConnection.getInstance().getDBcon();
    }
  //Implements the methods from the interface
    // get all suppliers
    public ArrayList getAllSuppliers(boolean retriveAssociation)
    {
        return miscWhere("", retriveAssociation);
    }
    //get one supplier having the ssn
    public Supplier findSupplier(String empssn, boolean retriveAssociation)
    {   String wClause = "  id = '" + empssn + "'";
        return singleWhere(wClause, retriveAssociation);
    }
    //find one supplier having the fname
    public Supplier searchSupplier(String attValue, boolean retriveAssociation)
    {
        String wClause = "name like '%" + attValue + "%'";
        System.out.println("SearchSupplier " + wClause);
        return singleWhere(wClause, retriveAssociation);
    }

     //insert a new supplier
    public int insertSupplier(Supplier emp)
    {  //call to get the next ssn number
        int nextID = GetMax.getMaxId("Select max(id) from Supplier");
        nextID = nextID + 1;
        System.out.println("next id = " +  nextID);

       int rc = -1;
	   String query="INSERT INTO Supplier(id, name, address,country,phoneno, email)  VALUES("+
	  		nextID + ",'" +
                        emp.getName()  + "','"  +
		    	emp.getAddress()  + "','"  +
                        emp.getCountry() + "', '" + emp.getPhoneno() + "','" + emp.getEmail() + "')";


      System.out.println("insert : " + query);
      try{ // insert new supplier
            Statement stmt = con.createStatement();
	 	  	stmt.setQueryTimeout(5);
	 	  	rc = stmt.executeUpdate(query);
	 	  	stmt.close();
		}//end try
	 	catch(Exception ex){
	 	  	System.out.println("Insert exception in supplier db: "+ex);
	   }
	   return(rc);
	}

	public int updateSupplier(Supplier emp)
	{
		Supplier empObj  = emp;
		int rc=-1;

		String query="UPDATE supplier SET "+
		 	  "name ='"+ empObj.getName()+"', "+
		 	  "address ='"+ empObj.getAddress() + "', " +
                          "country ='" + empObj.getCountry() + "', " +
                          "phoneno='" + emp.getPhoneno() + "', " +
                          "email='" + emp.getEmail() + "'" +
		          " WHERE id = "+ empObj.getId();
                System.out.println("Update query:" + query);
  		try{ // update supplier
	 		Statement stmt = con.createStatement();
	 		stmt.setQueryTimeout(5);
	 	 	rc = stmt.executeUpdate(query);

	 	 	stmt.close();
		}
	 	catch(Exception ex){
	 	 	System.out.println("Update exception in supplier db: "+ex);
	  	}
		return(rc);
	}

	public int delete(int id)
	{
               int rc=-1;

	  	String query="DELETE FROM supplier WHERE id =" +
				id;
                System.out.println(query);
	  	try{ // delete from supplier
	 		Statement stmt = con.createStatement();
	 		stmt.setQueryTimeout(5);
	 	  	rc = stmt.executeUpdate(query);
	 	  	stmt.close();
  		}
   	        catch(Exception ex){
	 	  	System.out.println("Delete exception in supplier db: "+ex);
   	        }
		return(rc);
	}

	//private methods
        //michWere is used whenever we want to select more than one supplier


	private ArrayList miscWhere(String wClause, boolean retrieveAssociation)
	{
		ResultSet results;
		ArrayList list = new ArrayList();

		String query =  buildQuery(wClause);

  		try{ // read the supplier from the database
	 		Statement stmt = con.createStatement();
	 		stmt.setQueryTimeout(5);
	 		results = stmt.executeQuery(query);

//			int id=0;
			while( results.next() ){
		     	 Supplier empObj = new Supplier();
			 empObj = buildSupplier(results);
                         list.add(empObj);
			}//end while
                        // if retrieveAssociation is tru, the supervisor and department
                        // has to be selected for each object in the list
			stmt.close();
		}
	 	catch(Exception e){
	 		System.out.println("Query exception - select: "+e);
			e.printStackTrace();
	 	}
		return list;
	}

	//Singelwhere is used when we only select one supplier
	private Supplier singleWhere(String wClause, boolean retrieveAssociation)
	{
		ResultSet results;
		Supplier empObj = new Supplier();
//        empObj.setId(0);
	    String query =  buildQuery(wClause);
        System.out.println(query);
		try{ // read the supplier from the database
	 		Statement stmt = con.createStatement();
	 		stmt.setQueryTimeout(5);
	 		results = stmt.executeQuery(query);

	 		if( results.next() ){
                            empObj = buildSupplier(results);
                            //assocaition is to be build
                            stmt.close();
//                            if(retrieveAssociation)
//                            {   //The supervisor and department is to be build as well
//                                String superssn = empObj.getSupervisor().getSsn();
//                                Supplier superEmp = singleWhere(" ssn = '" + superssn + "'",false);
//                                empObj.setSupervisor(superEmp);
//                                System.out.println("Supervisor er hentet");
//                                //here the department has to be build
//
//                            }
			}

		}//end try
	 	catch(Exception e){
	 		System.out.println("Query exception: "+e);
	 	}
		return empObj;
	}
	//method to build the query
        private String buildQuery(String wClause)
	{
	    String query="SELECT * FROM Supplier";

		if (wClause.length()>0)
			query=query+" WHERE "+ wClause;

		return query;
	}
	//method to build an supplier object
	private Supplier buildSupplier(ResultSet results)
      {   Supplier empObj = new Supplier();
          try{
                empObj.setId(results.getInt(1));
                empObj.setName(results.getString(2));
                empObj.setAddress(results.getString(3));
                empObj.setCountry(results.getString(4));
                empObj.setPhoneno(results.getString(5));
                empObj.setEmail(results.getString(6));

          }
         catch(Exception e)
         {
             System.out.println("error in building the supplier object");
         }
         return empObj;
      }

}

